﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using IDAL;
using Models;

namespace DAL
{
    public class WorksCategoryInfoDAL:IWorksCategoryInfo
    {
        private const string SQL_ALL_WORKSCATEGORY = @"select * from WorksCategoryInfo where CategoryID in
                             (select c.CategoryID from (select sum(CategoryID) as CCount,CategoryID from WorksInfo
                             group by CategoryID) c where c.CCount > 0)";

        public DataBase DB = new DataBase();

        public List<WorksCategoryInfo> GetAllWorksCategoryInfo()
        {
            List<WorksCategoryInfo> list = new List<WorksCategoryInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_ALL_WORKSCATEGORY);
            while (reader.Read())
            {
                WorksCategoryInfo workscategoryInfo = new WorksCategoryInfo();
                ReadtoWorksCategoryInfo(reader, workscategoryInfo);
                list.Add(workscategoryInfo);
            }
            DB.CloseDB();
            return list;
        }
        public List<WorksCategoryInfo> GetAdminWorksCategory()
        {
            string sql = "SELECT * FROM WorksCategoryInfo ";
            List<WorksCategoryInfo> list = new List<WorksCategoryInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                WorksCategoryInfo workscategoryInfo = new WorksCategoryInfo();
                ReadtoWorksCategoryInfo(reader, workscategoryInfo);
                list.Add(workscategoryInfo);
            }
            DB.CloseDB();
            return list;
        }
        public int GetPageCount(int pagesize)
        {
            DB.DatabaseConnection();
            DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            DB.SQLCommand.CommandType = CommandType.StoredProcedure;
            DB.SQLCommand.CommandText = "UP_WorksCategory_PAGECOUNT";
            DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
            DB.SQLCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            DB.SQLCommand.ExecuteNonQuery();
            int n = (int)DB.SQLCommand.Parameters["@RETURN_VALUE"].Value;
            DB.CloseDB();
            return n;
        }

        /// <summary>
        /// 用于Service页面显示works的分类
        /// </summary>
        /// <param name="pagesize">页面显示个数</param>
        /// <param name="nowpage">当前页面</param>
        /// <returns></returns>
        public List<WorksCategoryInfo> GetNowPageWorksCategoryInfo(int pagesize, int nowpage)
        {
            List<WorksCategoryInfo> list = new List<WorksCategoryInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_WorksCategory_PAGESELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                WorksCategoryInfo workscategoryInfo = new WorksCategoryInfo();
                ReadtoWorksCategoryInfo(reader, workscategoryInfo);
                list.Add(workscategoryInfo);
            }
            DB.CloseDB();
            return list;
        }

        public void ReadtoWorksCategoryInfo(SqlDataReader reader, WorksCategoryInfo workscategoryInfo)
        {
            try
            {
                workscategoryInfo.CategoryID = Int32.Parse(reader["CategoryID"].ToString());
            }
            catch
            { }
            
            workscategoryInfo.CategoryName =reader["CategoryName"] is DBNull?"":reader["CategoryName"].ToString();
            try
            {
                workscategoryInfo.HasNWork = Int32.Parse(reader["HasNWork"].ToString());
            }
            catch
            { }
            
            workscategoryInfo.CategoryPicURL =reader["CategoryPicURL"] is DBNull?"":reader["CategoryPicURL"].ToString();
            workscategoryInfo.IntroOne =reader["IntroOne"] is DBNull?"":reader["IntroOne"].ToString();
            workscategoryInfo.IntroTwo =reader["IntroTwo"] is DBNull?"":reader["IntroTwo"].ToString();
            workscategoryInfo.IntroThree =reader["IntroThree"] is DBNull?"":reader["IntroThree"].ToString();
        }

        public WorksCategoryInfo GetOneWorksCategory(int CategoryID)
        {
            WorksCategoryInfo wc = new WorksCategoryInfo();
            string sql = "SELECT * FROM WorksCategoryInfo WHERE CategoryID = " + CategoryID;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadtoWorksCategoryInfo(reader, wc);
            }
            return wc;
        }

        public int DeleteWorksCategoryInfo(int CategoryID)
        {
            int isSuccess = -1;
            string sql = "DELETE FROM WorksCategoryInfo WHERE CategoryID = " + CategoryID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int UpdateWorksCategoryInfo(WorksCategoryInfo wc)
        {
            int isSuccess = -1;
            string sql = @"UPDATE WorksCategoryInfo SET 
                          [CategoryName] = '" + wc.CategoryName + @"',
                          [CategoryPicURL] = '" + wc.CategoryPicURL + @"',
                          [HasNWork] = " + wc.HasNWork + @",
                          [IntroOne] = '" + wc.IntroOne + @"',
                          [IntroTwo] = '" + wc.IntroTwo + @"',
                          [IntroThree] = '" + wc.IntroThree + @"'
                          WHERE CategoryID = " + wc.CategoryID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public int AddWorksCategoryInfo(WorksCategoryInfo wc)
        {
            int isSuccess = -1;
            string sql = @"INSERT INTO WorksCategoryInfo ([CategoryName],[HasNWork],[CategoryPicURL],[IntroOne],[IntroTwo],[IntroThree])
                        VALUES('" + wc.CategoryName + "','" + wc.HasNWork + "','" + wc.CategoryPicURL + "','" + wc.IntroOne + "','" + wc.IntroTwo + "','" + wc.IntroThree + "');";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
    }
}
